CREATE OR REPLACE EDITIONABLE PACKAGE BODY "SCOTT"."PKG_INDEX" is

  /*------------- 插入上证指数，深证成指，中小板指数和创业板指数（只包括基础数据） --------------------*/
  procedure WRITE_STOCK_INDEX(p_code          in nvarchar2,
                              p_date          in date,
                              p_open_price    in number,
                              p_highest_price in number,
                              p_close_price   in number,
                              p_lowest_price  in number,
                              p_change_amount in number,
                              p_change_range  in number,
                              p_volume        in number,
                              p_turnover      in number) is
  begin
    INSERT into STOCK_INDEX
      (CODE_,
       DATE_,
       OPEN_PRICE,
       HIGHEST_PRICE,
       CLOSE_PRICE,
       LOWEST_PRICE,
       CHANGE_AMOUNT,
       CHANGE_RANGE,
       VOLUME,
       TURNOVER)
    values
      (p_code,
       p_date,
       p_open_price,
       p_highest_price,
       p_close_price,
       p_lowest_price,
       p_change_amount,
       p_change_range,
       p_volume,
       p_turnover);
    commit;
  end WRITE_STOCK_INDEX;

  /*----------- 插入上证指数，深证成指，中小板指数和创业板指数（包括基础数据和Hei Kin Ashi数据） --------*/
  procedure SAVE_STOCK_INDEX(p_code                 in nvarchar2,
                             p_date                 in date,
                             p_open_price           in number,
                             p_highest_price        in number,
                             p_close_price          in number,
                             p_lowest_price         in number,
                             p_change_amount        in number,
                             p_change_range         in number,
                             p_volume               in number,
                             p_turnover             in number,
                             p_ma5                  in number,
                             p_ma10                 in number,
                             p_ma20                 in number,
                             p_ma60                 in number,
                             p_ma120                in number,
                             p_ma250                in number,
                             ha_index_open_price    in number,
                             ha_index_highest_price in number,
                             ha_index_close_price   in number,
                             ha_index_lowest_price  in number,
                             p_bias5                in number,
                             p_bias10               in number,
                             p_bias20               in number,
                             p_bias60               in number,
                             p_bias120              in number,
                             p_bias250              in number) is
  begin
    INSERT into STOCK_INDEX
      (CODE_,
       DATE_,
       OPEN_PRICE,
       HIGHEST_PRICE,
       CLOSE_PRICE,
       LOWEST_PRICE,
       CHANGE_AMOUNT,
       CHANGE_RANGE,
       VOLUME,
       TURNOVER,
       MA5,
       MA10,
       MA20,
       MA60,
       MA120,
       MA250,
       HA_INDEX_OPEN_PRICE,
       HA_INDEX_HIGHEST_PRICE,
       HA_INDEX_CLOSE_PRICE,
       HA_INDEX_LOWEST_PRICE,
       BIAS5,
       BIAS10,
       BIAS20,
       BIAS60,
       BIAS120,
       BIAS250)
    values
      (p_code,
       p_date,
       p_open_price,
       p_highest_price,
       p_close_price,
       p_lowest_price,
       p_change_amount,
       p_change_range,
       p_volume,
       p_turnover,
       p_ma5,
       p_ma10,
       p_ma20,
       p_ma60,
       p_ma120,
       p_ma250,
       ha_index_open_price,
       ha_index_highest_price,
       ha_index_close_price,
       ha_index_lowest_price,
       p_bias5,
       p_bias10,
       p_bias20,
       p_bias60,
       p_bias120,
       p_bias250);
    commit;
  end SAVE_STOCK_INDEX;

  /*---------------------------------------------------- calculate five moving average -------------------------------------------------*/
  procedure WRITE_FIVE is
  begin
    ------------------------------------------ standard declare section ---------------------------------------------------
    declare
      -- 表示code_
      v_code varchar2(10);
      -- 表示5天收盘价的和
      v_five_sum number := 0;
      -- 表示5天收盘价的平均值
      v_five_average number := 0;
      -- 定义一个含有5个数值型数据的数组
      type type_array is varray(5) of number;
      array_five type_array := type_array();
      -- define cursor section.返回全部stock_code
      cursor cur_all_stock_index_code is
        select distinct t.code_ from stock_index t order by t.code_ asc;
      cursor cur_all_stock_index_c_p is
        select t.close_price, t.date_
          from stock_index t
         where t.code_ = v_code
         order by t.date_ asc;
      ------------------------------------------ standard declare section ---------------------------------------------------
    
    begin
      --------------------------------- standard for loop section --------------------------------------------------------
      for i in cur_all_stock_index_code loop
        v_code := i.code_;
        -- dbms_output.put_line('**************   v_code'||'  :  '||v_code);
        array_five := type_array();
        for j in cur_all_stock_index_c_p loop
          array_five.extend; -- 扩展数组，扩展一个元素
          array_five(array_five.count) := j.close_price;
          if mod(array_five.count, 5) = 0 then
            -- dbms_output.put_line('j.date_'||'  :  '||j.date_);
            v_five_sum := 0;
            for x in 1 .. array_five.count loop
              -- 求5天收盘价的和
              v_five_sum := v_five_sum + array_five(x);
            end loop;
            -- dbms_output.put_line('v_five_sum'||'  :  '||v_five_sum);
            -- 删除数组中的第一个元素，将其与4个元素向前挪一位，并删除下标为5的元素
            for y in 1 .. array_five.count - 1 loop
              array_five(y) := array_five(y + 1);
            end loop;
            array_five.trim;
            -- 5天收盘价的平均值
            v_five_average := v_five_sum / 5;
            -- 向所有记录的FIVE列插入5天收盘价的平均值
            update stock_index t
               set t.ma5 = round(v_five_average, 2)
             where t.code_ = v_code
               and t.date_ = j.date_;
            -- dbms_output.put_line('v_five_average'||'  :  '||v_five_average);
          end if;
        end loop;
      end loop;
    end;
  end WRITE_FIVE;
  -- end procedure

  /*---------------------------------------------------- calculate ten moving average -------------------------------------------------*/
  procedure WRITE_TEN is
  begin
    ------------------------------------------ standard declare section ---------------------------------------------------
    declare
      -- 表示code_
      v_code varchar2(10);
      -- 表示10天收盘价的和
      v_ten_sum number := 0;
      -- 表示10天收盘价的平均值
      v_ten_average number := 0;
      -- 定义一个含有10个数值型数据的数组
      type type_array is varray(10) of number;
      array_ten type_array := type_array();
      -- define cursor section.返回全部stock_code
      cursor cur_all_stock_index_code is
        select distinct t.code_ from stock_index t order by t.code_ asc;
      cursor cur_all_stock_index_c_p is
        select t.close_price, t.date_
          from stock_index t
         where t.code_ = v_code
         order by t.date_ asc;
      ------------------------------------------ standard declare section ---------------------------------------------------
    
    begin
      --------------------------------- standard for loop section --------------------------------------------------------
      for i in cur_all_stock_index_code loop
        v_code := i.code_;
        dbms_output.put_line('**************   v_code' || '  :  ' ||
                             v_code);
        array_ten := type_array();
        for j in cur_all_stock_index_c_p loop
          array_ten.extend; -- 扩展数组，扩展一个元素
          array_ten(array_ten.count) := j.close_price;
          if mod(array_ten.count, 10) = 0 then
            dbms_output.put_line('j.date_' || '  :  ' || j.date_);
            v_ten_sum := 0;
            for x in 1 .. array_ten.count loop
              -- 求10天收盘价的和
              v_ten_sum := v_ten_sum + array_ten(x);
            end loop;
            dbms_output.put_line('ten_sum' || '  :  ' || v_ten_sum);
            -- 删除数组中的第一个元素，将其与9个元素向前挪一位，并删除下标为10的元素
            for y in 1 .. array_ten.count - 1 loop
              array_ten(y) := array_ten(y + 1);
            end loop;
            array_ten.trim;
            -- 10天收盘价的平均值
            v_ten_average := v_ten_sum / 10;
            -- 向所有记录的FIVE列插入10天收盘价的平均值
            update stock_index t
               set t.ma10 = round(v_ten_average, 2)
             where t.code_ = v_code
               and t.date_ = j.date_;
            dbms_output.put_line('v_ten_average' || '  :  ' ||
                                 v_ten_average);
          end if;
        end loop;
      end loop;
    end;
  end WRITE_TEN;
  -- end procedure

  /*---------------------------------------------------- calculate twenty moving average -------------------------------------------------*/
  procedure WRITE_TWENTY is
  begin
    ------------------------------------------ standard declare section ---------------------------------------------------
    declare
      -- 表示code_
      v_code varchar2(10);
      -- 表示20天收盘价的和
      v_twenty_sum number := 0;
      -- 表示20天收盘价的平均值
      v_twenty_average number := 0;
      -- 定义一个含有20个数值型数据的数组
      type type_array is varray(20) of number;
      array_twenty type_array := type_array();
      -- define cursor section.返回全部stock_code
      cursor cur_all_stock_index_code is
        select distinct t.code_ from stock_index t order by t.code_ asc;
      cursor cur_all_stock_index_c_p is
        select t.close_price, t.date_
          from stock_index t
         where t.code_ = v_code
         order by t.date_ asc;
      ------------------------------------------ standard declare section ---------------------------------------------------
    
    begin
      --------------------------------- standard for loop section --------------------------------------------------------
      for i in cur_all_stock_index_code loop
        v_code := i.code_;
        dbms_output.put_line('**************   v_code' || '  :  ' ||
                             v_code);
        array_twenty := type_array();
        for j in cur_all_stock_index_c_p loop
          array_twenty.extend; -- 扩展数组，扩展一个元素
          array_twenty(array_twenty.count) := j.close_price;
          if mod(array_twenty.count, 20) = 0 then
            dbms_output.put_line('j.date_' || '  :  ' || j.date_);
            v_twenty_sum := 0;
            for x in 1 .. array_twenty.count loop
              -- 求20天收盘价的和
              v_twenty_sum := v_twenty_sum + array_twenty(x);
            end loop;
            dbms_output.put_line('v_twenty_sum' || '  :  ' || v_twenty_sum);
            -- 删除数组中的第一个元素，将其与19个元素向前挪一位，并删除下标为20的元素
            for y in 1 .. array_twenty.count - 1 loop
              array_twenty(y) := array_twenty(y + 1);
            end loop;
            array_twenty.trim;
            -- 20天收盘价的平均值
            v_twenty_average := v_twenty_sum / 20;
            -- 向所有记录的FIVE列插入20天收盘价的平均值
            update stock_index t
               set t.ma20 = round(v_twenty_average, 2)
             where t.code_ = v_code
               and t.date_ = j.date_;
            dbms_output.put_line('v_twenty_average' || '  :  ' ||
                                 v_twenty_average);
          end if;
        end loop;
      end loop;
    end;
  end WRITE_TWENTY;
  -- end procedure

  /*---------------------------------------------------- calculate sixty moving average -------------------------------------------------*/
  procedure WRITE_SIXTY is
  begin
    ------------------------------------------ standard declare section ---------------------------------------------------
    declare
      -- 表示code_
      v_code varchar2(10);
      -- 表示60天收盘价的和
      v_sixty_sum number := 0;
      -- 表示60天收盘价的平均值
      v_sixty_average number := 0;
      -- 定义一个含有60个数值型数据的数组
      type type_array is varray(60) of number;
      array_sixty type_array := type_array();
      -- define cursor section.返回全部stock_code
      cursor cur_all_stock_index_code is
        select distinct t.code_ from stock_index t order by t.code_ asc;
      cursor cur_all_stock_index_c_p is
        select t.close_price, t.date_
          from stock_index t
         where t.code_ = v_code
         order by t.date_ asc;
      ------------------------------------------ standard declare section ---------------------------------------------------
    
    begin
      --------------------------------- standard for loop section --------------------------------------------------------
      for i in cur_all_stock_index_code loop
        v_code := i.code_;
        dbms_output.put_line('**************   v_code' || '  :  ' ||
                             v_code);
        array_sixty := type_array();
        for j in cur_all_stock_index_c_p loop
          array_sixty.extend; -- 扩展数组，扩展一个元素
          array_sixty(array_sixty.count) := j.close_price;
          if mod(array_sixty.count, 60) = 0 then
            dbms_output.put_line('j.date_' || '  :  ' || j.date_);
            v_sixty_sum := 0;
            for x in 1 .. array_sixty.count loop
              -- 求60天收盘价的和
              v_sixty_sum := v_sixty_sum + array_sixty(x);
            end loop;
            dbms_output.put_line('v_sixty_sum' || '  :  ' || v_sixty_sum);
            -- 删除数组中的第一个元素，将其与59个元素向前挪一位，并删除下标为60的元素
            for y in 1 .. array_sixty.count - 1 loop
              array_sixty(y) := array_sixty(y + 1);
            end loop;
            array_sixty.trim;
            -- 60天收盘价的平均值
            v_sixty_average := v_sixty_sum / 60;
            -- 向所有记录的FIVE列插入60天收盘价的平均值
            update stock_index t
               set t.ma60 = round(v_sixty_average, 2)
             where t.code_ = v_code
               and t.date_ = j.date_;
            dbms_output.put_line('v_sixty_average' || '  :  ' ||
                                 v_sixty_average);
          end if;
        end loop;
      end loop;
    end;
  end WRITE_SIXTY;
  -- end procedure

  /*---------------------------------------------------- calculate one hundred twenty moving average -------------------------------------------------*/
  procedure WRITE_ONEHUNDREDTWENTY is
  begin
    ------------------------------------------ standard declare section ---------------------------------------------------
    declare
      -- 表示code_
      v_code varchar2(10);
      -- 表示120天收盘价的和
      v_one_hundred_twenty_sum number := 0;
      -- 表示120天收盘价的平均值
      v_one_hundred_twenty_average number := 0;
      -- 定义一个含有120个数值型数据的数组
      type type_array is varray(120) of number;
      array_one_hundred_twenty type_array := type_array();
      -- define cursor section.返回全部stock_code
      cursor cur_all_stock_index_code is
        select distinct t.code_ from stock_index t order by t.code_ asc;
      cursor cur_all_stock_index_c_p is
        select t.close_price, t.date_
          from stock_index t
         where t.code_ = v_code
         order by t.date_ asc;
      ------------------------------------------ standard declare section ---------------------------------------------------
    
    begin
      --------------------------------- standard for loop section --------------------------------------------------------
      for i in cur_all_stock_index_code loop
        v_code := i.code_;
        dbms_output.put_line('**************   v_code' || '  :  ' ||
                             v_code);
        array_one_hundred_twenty := type_array();
        for j in cur_all_stock_index_c_p loop
          array_one_hundred_twenty.extend; -- 扩展数组，扩展一个元素
          array_one_hundred_twenty(array_one_hundred_twenty.count) := j.close_price;
          if mod(array_one_hundred_twenty.count, 120) = 0 then
            dbms_output.put_line('j.date_' || '  :  ' || j.date_);
            v_one_hundred_twenty_sum := 0;
            for x in 1 .. array_one_hundred_twenty.count loop
              -- 求120天收盘价的和
              v_one_hundred_twenty_sum := v_one_hundred_twenty_sum +
                                          array_one_hundred_twenty(x);
            end loop;
            dbms_output.put_line('v_one_hundred_twenty_sum' || '  :  ' ||
                                 v_one_hundred_twenty_sum);
            -- 删除数组中的第一个元素，将其与119个元素向前挪一位，并删除下标为120的元素
            for y in 1 .. array_one_hundred_twenty.count - 1 loop
              array_one_hundred_twenty(y) := array_one_hundred_twenty(y + 1);
            end loop;
            array_one_hundred_twenty.trim;
            -- 120天收盘价的平均值
            v_one_hundred_twenty_average := v_one_hundred_twenty_sum / 120;
            -- 向所有记录的FIVE列插入120天收盘价的平均值
            update stock_index t
               set t.ma120 = round(v_one_hundred_twenty_average, 2)
             where t.code_ = v_code
               and t.date_ = j.date_;
            dbms_output.put_line('v_one_hundred_twenty_average' || '  :  ' ||
                                 v_one_hundred_twenty_average);
          end if;
        end loop;
      end loop;
    end;
  end WRITE_ONEHUNDREDTWENTY;
  -- end procedure

  /*---------------------------------------------------- calculate one hundred fifty moving average -------------------------------------------------*/
  procedure WRITE_TWOHUNDREDFIFTY is
  begin
    ------------------------------------------ standard declare section ---------------------------------------------------
    declare
      -- 表示code_
      v_code varchar2(10);
      -- 表示250天收盘价的和
      v_one_hundred_fifty_sum number := 0;
      -- 表示250天收盘价的平均值
      v_one_hundred_fifty_average number := 0;
      -- 定义一个含有250个数值型数据的数组
      type type_array is varray(250) of number;
      array_one_hundred_fifty type_array := type_array();
      -- define cursor section.返回全部stock_code
      cursor cur_all_stock_index_code is
        select distinct t.code_ from stock_index t order by t.code_ asc;
      cursor cur_all_stock_index_c_p is
        select t.close_price, t.date_
          from stock_index t
         where t.code_ = v_code
         order by t.date_ asc;
      ------------------------------------------ standard declare section ---------------------------------------------------
    
    begin
      --------------------------------- standard for loop section --------------------------------------------------------
      for i in cur_all_stock_index_code loop
        v_code := i.code_;
        dbms_output.put_line('**************   v_code' || '  :  ' ||
                             v_code);
        array_one_hundred_fifty := type_array();
        for j in cur_all_stock_index_c_p loop
          array_one_hundred_fifty.extend; -- 扩展数组，扩展一个元素
          array_one_hundred_fifty(array_one_hundred_fifty.count) := j.close_price;
          if mod(array_one_hundred_fifty.count, 250) = 0 then
            dbms_output.put_line('j.date_' || '  :  ' || j.date_);
            v_one_hundred_fifty_sum := 0;
            for x in 1 .. array_one_hundred_fifty.count loop
              -- 求250天收盘价的和
              v_one_hundred_fifty_sum := v_one_hundred_fifty_sum +
                                         array_one_hundred_fifty(x);
            end loop;
            dbms_output.put_line('v_one_hundred_fifty_sum' || '  :  ' ||
                                 v_one_hundred_fifty_sum);
            -- 删除数组中的第一个元素，将其与249个元素向前挪一位，并删除下标为250的元素
            for y in 1 .. array_one_hundred_fifty.count - 1 loop
              array_one_hundred_fifty(y) := array_one_hundred_fifty(y + 1);
            end loop;
            array_one_hundred_fifty.trim;
            -- 250天收盘价的平均值
            v_one_hundred_fifty_average := v_one_hundred_fifty_sum / 250;
            -- 向所有记录的FIVE列插入250天收盘价的平均值
            update stock_index t
               set t.ma250 = round(v_one_hundred_fifty_average, 2)
             where t.code_ = v_code
               and t.date_ = j.date_;
            dbms_output.put_line('v_one_hundred_fifty_average' || '  :  ' ||
                                 v_one_hundred_fifty_average);
          end if;
        end loop;
      end loop;
    end;
  end WRITE_TWOHUNDREDFIFTY;
  -- end procedure

  /*---- 计算上证指数，深证成指，中小板指数和创业板指数的hei kin ashi的开盘价，收盘价，最高价和最低价 ----*/
  procedure WRITE_STOCK_INDEX_HA is
    -- 代码
    v_index_code varchar2(10);
    -- 日期
    v_index_date date;
    -- 获取所有的CODE
    cursor cur_all_index_code is
      select distinct t.code_ index_code from stock_index t;
    -- 查询某个具体的指数的第一条交易记录
    cursor cur_first_stock_index is
      select *
        from (select *
                from stock_index t
               where t.code_ = v_index_code
               order by t.date_ asc)
       where rownum <= 1;
    -- 查询某个指数除了最早的一条记录外的其他记录，并按升序排列
    cursor cur_later_stock_index is
      select *
        from stock_index t
       where t.code_ = v_index_code
         and t.date_ > v_index_date
       order by t.date_ asc;
    -- 定义表stock_index结构的游标变量
    first_stock_index stock_index%rowtype;
    later_stock_index stock_index%rowtype;
    pre_stock_index   stock_index%rowtype;
    -- 用于计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价的变量
    v_ha_index_open_price    number;
    v_ha_index_close_price   number;
    v_ha_index_highest_price number;
    v_ha_index_lowest_price  number;
  begin
    open cur_all_index_code;
    loop
      -- 获取每个指数的code_字段
      fetch cur_all_index_code
        into v_index_code;
      exit when cur_all_index_code%notfound;
    
      -- 先计算每个指数的第一条记录
      open cur_first_stock_index;
      fetch cur_first_stock_index
        into first_stock_index;
      exit when cur_first_stock_index%notfound;
    
      -- 计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价
      v_ha_index_open_price  := (first_stock_index.open_price +
                                first_stock_index.close_price) / 2;
      v_ha_index_close_price := (first_stock_index.open_price +
                                first_stock_index.close_price +
                                first_stock_index.highest_price +
                                first_stock_index.lowest_price) / 4;
      if first_stock_index.highest_price > v_ha_index_open_price then
        v_ha_index_highest_price := first_stock_index.highest_price;
      else
        v_ha_index_highest_price := v_ha_index_open_price;
      end if;
      if first_stock_index.lowest_price < v_ha_index_open_price then
        v_ha_index_lowest_price := first_stock_index.lowest_price;
      else
        v_ha_index_lowest_price := v_ha_index_open_price;
      end if;
      -- 保存hei kin ashi平均K线开盘价，收盘价，最高价和最低价
      update stock_index t
         set t.ha_index_open_price    = v_ha_index_open_price,
             t.ha_index_close_price   = v_ha_index_close_price,
             t.ha_index_highest_price = v_ha_index_highest_price,
             t.ha_index_lowest_price  = v_ha_index_lowest_price
       where t.code_ = first_stock_index.code_
         and t.date_ = first_stock_index.date_;
      commit;
      v_index_date := first_stock_index.date_;
      close cur_first_stock_index;
    
      -- 再计算每个指数的其他记录
      open cur_later_stock_index;
      loop
        fetch cur_later_stock_index
          into later_stock_index;
        exit when cur_later_stock_index%notfound;
      
        -- 前一条记录
        select *
          into pre_stock_index
          from (select *
                  from stock_index t
                 where t.code_ = v_index_code
                   and t.date_ <= v_index_date
                 order by t.date_ desc)
         where rownum <= 1;
      
        -- 计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价
        v_ha_index_open_price  := (pre_stock_index.ha_index_open_price +
                                  pre_stock_index.ha_index_close_price) / 2;
        v_ha_index_close_price := (later_stock_index.open_price +
                                  later_stock_index.close_price +
                                  later_stock_index.highest_price +
                                  later_stock_index.lowest_price) / 4;
        if later_stock_index.highest_price > v_ha_index_open_price then
          v_ha_index_highest_price := later_stock_index.highest_price;
        else
          v_ha_index_highest_price := v_ha_index_open_price;
        end if;
        if later_stock_index.lowest_price < v_ha_index_open_price then
          v_ha_index_lowest_price := later_stock_index.lowest_price;
        else
          v_ha_index_lowest_price := v_ha_index_open_price;
        end if;
        -- 保存hei kin ashi平均K线开盘价，收盘价，最高价和最低价
        update stock_index t
           set t.ha_index_open_price    = v_ha_index_open_price,
               t.ha_index_close_price   = v_ha_index_close_price,
               t.ha_index_highest_price = v_ha_index_highest_price,
               t.ha_index_lowest_price  = v_ha_index_lowest_price
         where t.code_ = later_stock_index.code_
           and t.date_ = later_stock_index.date_;
        commit;
      
        -- 把这次的数据留给下一次迭代使用
        v_index_date := later_stock_index.date_;
      end loop;
      close cur_later_stock_index;
    
    end loop;
    close cur_all_index_code;
  end WRITE_STOCK_INDEX_HA;

  /*---------------------------------------------------- 计算所有指数的乖离率 -------------------------------------------------*/
  procedure WRITE_STOCK_INDEX_BIAS is
  begin
    declare
      -- 表示code_
      v_code varchar2(10);
      -- 5日乖离率
      v_bias5 number := null;
      -- 10日乖离率
      v_bias10 number := null;
      -- 20日乖离率
      v_bias20 number := null;
      -- 60日乖离率
      v_bias60 number := null;
      -- 120日乖离率
      v_bias120 number := null;
      -- 250日乖离率
      v_bias250 number := null;
      -- 交易日期
      v_date date;
      -- 获取指数的code_，排除重复
      cursor cur_all_index_code is
        select distinct t.code_ from stock_index t;
      -- 根据v_code，获取这个指数的所有数据，并按照升序排列
      cursor cur_single_stock_index is
        select *
          from stock_index t
         where t.code_ = v_code
         order by t.date_ asc;
    begin
      for i in cur_all_index_code loop
        v_code := i.code_;
        for j in cur_single_stock_index loop
          v_date := j.date_;
          -- 5日乖离率
          if j.ma5 is not null then
            v_bias5 := (j.close_price - j.ma5) / j.ma5 * 100;
          end if;
          -- 10日乖离率
          if j.ma10 is not null then
            v_bias10 := (j.close_price - j.ma10) / j.ma10 * 100;
          end if;
          -- 20日乖离率
          if j.ma20 is not null then
            v_bias20 := (j.close_price - j.ma20) / j.ma20 * 100;
          end if;
          -- 60日乖离率
          if j.ma60 is not null then
            v_bias60 := (j.close_price - j.ma60) / j.ma60 * 100;
          end if;
          -- 120日乖离率
          if j.ma120 is not null then
            v_bias120 := (j.close_price - j.ma120) / j.ma120 * 100;
          end if;
          -- 250日乖离率
          if j.ma250 is not null then
            v_bias250 := (j.close_price - j.ma250) / j.ma250 * 100;
          end if;
        
          -- 更新
          update stock_index t
             set t.bias5   = v_bias5,
                 t.bias10  = v_bias10,
                 t.bias20  = v_bias20,
                 t.bias60  = v_bias60,
                 t.bias120 = v_bias120,
                 t.bias250 = v_bias250
           where t.code_ = v_code
             and t.date_ = v_date;
        end loop;
        commit;
      end loop;
    end;
  end WRITE_STOCK_INDEX_BIAS;

  /*---------------------------------------------------- write moving average by date -------------------------------------------------*/
  procedure WRITE_STOCK_INDEX_MA_BY_DATE(p_date in varchar2) is
  begin
    ------------------------------------------ standard declare section ---------------------------------------------------
    declare
      -- 表示code_
      v_code varchar2(10);
      -- 表示5天收盘价的平均值
      v_five_average number := 0;
      -- 表示10天收盘价的平均值
      v_ten_average number := 0;
      -- 表示20天收盘价的平均值
      v_twenty_average number := 0;
      -- 表示60天收盘价的平均值
      v_sixty_average number := 0;
      -- 表示120天收盘价的平均值
      v_one_hundred_twenty_average number := 0;
      -- 表示250天收盘价的平均值
      v_two_hundred_fifty_average number := 0;
      -- 表示所有天收盘价的平均值
      --infiniteAverage number:=0;
      -- define cursor section.返回全部code_字段
      cursor cur_all_index_code is
        select distinct t.code_ from stock_index t order by t.code_ asc;
      ------------------------------------------ standard declare section ---------------------------------------------------
    
    begin
      --------------------------------- standard for loop section --------------------------------------------------------
      for i in cur_all_index_code loop
        v_code := i.code_;
        -- dbms_output.put_line('**************   v_code'||'  :  '||v_code);
      
        -- 更新所有指数某一天的5日均线
        select avg(d.close_price)
          into v_five_average
          from (select *
                  from (select *
                          from stock_index t
                         where t.code_ = v_code
                           and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
                         order by t.date_ desc)
                 where rownum <= 5) d;
        update stock_index t
           set t.ma5 = round(v_five_average, 2)
         where t.code_ = v_code
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
      
        -- 更新所有指数某一天的10日均线
        select avg(d.close_price)
          into v_ten_average
          from (select *
                  from (select *
                          from stock_index t
                         where t.code_ = v_code
                           and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
                         order by t.date_ desc)
                 where rownum <= 10) d;
        update stock_index t
           set t.ma10 = round(v_ten_average, 2)
         where t.code_ = v_code
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
      
        -- 更新所有指数某一天的20日均线
        select avg(d.close_price)
          into v_twenty_average
          from (select *
                  from (select *
                          from stock_index t
                         where t.code_ = v_code
                           and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
                         order by t.date_ desc)
                 where rownum <= 20) d;
        update stock_index t
           set t.ma20 = round(v_twenty_average, 2)
         where t.code_ = v_code
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
      
        -- 更新所有指数某一天的60日均线
        select avg(d.close_price)
          into v_sixty_average
          from (select *
                  from (select *
                          from stock_index t
                         where t.code_ = v_code
                           and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
                         order by t.date_ desc)
                 where rownum <= 60) d;
        update stock_index t
           set t.ma60 = round(v_sixty_average, 2)
         where t.code_ = v_code
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
      
        -- 更新所有指数某一天的120日均线
        select avg(d.close_price)
          into v_one_hundred_twenty_average
          from (select *
                  from (select *
                          from stock_index t
                         where t.code_ = v_code
                           and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
                         order by t.date_ desc)
                 where rownum <= 120) d;
        update stock_index t
           set t.ma120 = round(v_one_hundred_twenty_average, 2)
         where t.code_ = v_code
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
      
        -- 更新所有股票某一天的250日均线
        select avg(d.close_price)
          into v_two_hundred_fifty_average
          from (select *
                  from (select *
                          from stock_index t
                         where t.code_ = v_code
                           and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
                         order by t.date_ desc)
                 where rownum <= 250) d;
        update stock_index t
           set t.ma250 = round(v_two_hundred_fifty_average, 2)
         where t.code_ = v_code
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
      
      -- 更新所有股票某一天的所有日均线
      /*select avg(d.stock_close) into infiniteAverage from(
                                                     select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') order by t.stock_date desc
                                              ) d;
                                              update stock_moving_average t set t.infinite=round(infiniteAverage,2) where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
                                              */
      end loop;
    end;
  end WRITE_STOCK_INDEX_MA_BY_DATE;
  -- end procedure

  /*------ 按日期，计算上证指数，深证成指，中小板指数和创业板指数的hei kin ashi的开盘价，收盘价，最高价和最低价 -------*/
  procedure WRITE_STOCK_INDEX_HA_BY_DATE(p_date in varchar2) is
    -- 获取所有的CODE
    cursor cur_all_index_code is
      select distinct t.code_ index_code from stock_index t;
    -- 表示CODE类型的变量
    v_index_code varchar2(10);
  
    -- 查询某个指数在日期p_date之前的那一条记录
    cursor cur_all_stock_index is
      select *
        from (select *
                from (select *
                        from stock_index t
                       where t.code_ = v_index_code
                         and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
                       order by t.date_ desc)
               where rownum <= 2) t2
       where t2.date_ <>
             (select t1.date_
                from stock_index t1
               where t1.code_ = v_index_code
                 and t1.date_ = to_date(p_date, 'yyyy-mm-dd'));
    -- 定义表stock_index结构的游标变量
    all_stock_index stock_index%rowtype;
  
    -- 查询某个具体的指数的某一日交易记录
    cursor cur_later_stock_index is
      select *
        from stock_index t
       where t.code_ = v_index_code
         and t.date_ = to_date(p_date, 'yyyy-mm-dd');
    -- 定义表stock_index结构的游标变量
    later_stock_index stock_index%rowtype;
  
    -- 用于计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价的变量
    v_ha_index_open_price    number;
    v_ha_index_close_price   number;
    v_ha_index_highest_price number;
    v_ha_index_lowest_price  number;
  begin
    open cur_all_index_code;
    loop
      -- 获取每个指数的code_字段
      fetch cur_all_index_code
        into v_index_code;
      exit when cur_all_index_code%notfound;
    
      open cur_all_stock_index;
      open cur_later_stock_index;
      loop
        fetch cur_all_stock_index
          into all_stock_index;
        exit when cur_all_stock_index%notfound;
      
        fetch cur_later_stock_index
          into later_stock_index;
        exit when cur_later_stock_index%notfound;
      
        -- 计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价
        v_ha_index_open_price  := (all_stock_index.ha_index_open_price +
                                  all_stock_index.ha_index_close_price) / 2;
        v_ha_index_close_price := (later_stock_index.open_price +
                                  later_stock_index.close_price +
                                  later_stock_index.highest_price +
                                  later_stock_index.lowest_price) / 4;
        if later_stock_index.highest_price > v_ha_index_open_price then
          v_ha_index_highest_price := later_stock_index.highest_price;
        else
          v_ha_index_highest_price := v_ha_index_open_price;
        end if;
        if later_stock_index.lowest_price < v_ha_index_open_price then
          v_ha_index_lowest_price := later_stock_index.lowest_price;
        else
          v_ha_index_lowest_price := v_ha_index_open_price;
        end if;
        -- 保存hei kin ashi平均K线开盘价，收盘价，最高价和最低价
        update stock_index t
           set t.ha_index_open_price    = v_ha_index_open_price,
               t.ha_index_close_price   = v_ha_index_close_price,
               t.ha_index_highest_price = v_ha_index_highest_price,
               t.ha_index_lowest_price  = v_ha_index_lowest_price
         where t.code_ = later_stock_index.code_
           and t.date_ = later_stock_index.date_;
        commit;
      end loop;
      close cur_all_stock_index;
      close cur_later_stock_index;
    
    end loop;
    close cur_all_index_code;
  end WRITE_STOCK_INDEX_HA_BY_DATE;

  /*------------------------ 按照日期，计算所有指数在某一日的乖离率 -----------------------*/
  procedure WRITE_STOCK_INDEX_BIAS_BY_DATE(p_date in varchar2) is
    -- 表示CODE_类型的变量
    v_code varchar2(10);
    -- 5日乖离率
    v_bias5 number := null;
    -- 10日乖离率
    v_bias10 number := null;
    -- 20日乖离率
    v_bias20 number := null;
    -- 60日乖离率
    v_bias60 number := null;
    -- 120日乖离率
    v_bias120 number := null;
    -- 250日乖离率
    v_bias250 number := null;
  
    row_stock_index stock_index%rowtype;
    -- 获取所有指数的code_，排除重复的
    cursor cur_all_stock_index_code is
      select distinct t.code_ from stock_index t;
    -- 某个指数某一天的交易记录
    cursor cur_single_stock_index is
      select *
        from stock_index t
       where t.code_ = v_code
         and t.date_ = to_date(p_date, 'yyyy-mm-dd');
  begin
    open cur_all_stock_index_code;
    loop
      -- 获取每个指数的code_字段
      fetch cur_all_stock_index_code
        into v_code;
      exit when cur_all_stock_index_code%notfound;
    
      open cur_single_stock_index;
      loop
        -- 获取每个指数在某一日的交易记录
        fetch cur_single_stock_index
          into row_stock_index;
        exit when cur_single_stock_index%notfound;
      
        -- 计算5日乖离率、10日乖离率、20日乖离率、60日乖离率、120日乖离率、250日乖离率
        v_bias5   := (row_stock_index.close_price - row_stock_index.ma5) /
                     row_stock_index.ma5 * 100;
        v_bias10  := (row_stock_index.close_price - row_stock_index.ma10) /
                     row_stock_index.ma10 * 100;
        v_bias20  := (row_stock_index.close_price - row_stock_index.ma20) /
                     row_stock_index.ma20 * 100;
        v_bias60  := (row_stock_index.close_price - row_stock_index.ma60) /
                     row_stock_index.ma60 * 100;
        v_bias120 := (row_stock_index.close_price - row_stock_index.ma120) /
                     row_stock_index.ma120 * 100;
        v_bias250 := (row_stock_index.close_price - row_stock_index.ma250) /
                     row_stock_index.ma250 * 100;
      
        -- 更新某个指数在某一日的5日乖离率、10日乖离率、20日乖离率、60日乖离率、120日乖离率、250日乖离率
        update stock_index t
           set t.bias5   = v_bias5,
               t.bias10  = v_bias10,
               t.bias20  = v_bias20,
               t.bias60  = v_bias60,
               t.bias120 = v_bias120,
               t.bias250 = v_bias250
         where t.code_ = v_code
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
      
      end loop;
      close cur_single_stock_index;
    
      commit;
    
    end loop;
    close cur_all_stock_index_code;
  end WRITE_STOCK_INDEX_BIAS_BY_DATE;

  /*----------------------------------- write MACD of all stock index --------------------------------*/
  procedure WRITE_MACD_INIT as
    -- 表示code_
    v_code varchar2(10);
    -- define cursor section.返回全部code_
    cursor cur_all_stock_index_code is
      select distinct t.code_ from stock_index t order by t.code_ asc;
    -- 获取每只股票第一个交易日的日期
    cursor cur_first_stock_index_date is
      select min(t.date_) as date_
        from stock_index t
       where t.code_ = v_code;
  begin
    -- 初始化每只股票第一个交易日的ema12,ema26,dif和dea字段
    for i in cur_all_stock_index_code loop
      v_code := i.code_;
      for j in cur_first_stock_index_date loop
        update stock_index t
           set t.ema12 = t.close_price,
               t.ema26 = t.close_price,
               t.dif   = 0,
               t.dea   = 0
         where t.code_ = v_code
           and t.date_ = j.date_;
        commit;
      end loop;
    end loop;
  end WRITE_MACD_INIT;

  procedure WRITE_MACD_EMA as
    v_pre_ema12  number;
    v_pre_ema26  number;
    v_pre_dif    number;
    v_pre_dea    number;
    v_first_date date;
    -- 表示stock_code
    v_code varchar2(10);
    -- define cursor section.返回全部stock_code
    cursor cur_all_stock_index_code is
      select distinct t.code_ from stock_index t order by t.code_ asc;
    -- 获取每只股票第一个交易日的日期
    cursor cur_first_stock_index_date is
      select min(t.date_) as date_
        from stock_index t
       where t.code_ = v_code;
    -- 根据v_code选出某只股票的除第一天以外的全部交易记录，按升序排列
    cursor cur_all_stock_index is
      select distinct *
        from stock_index t
       where t.code_ = v_code
         and t.date_ > v_first_date
       order by t.date_ asc;
  begin
    -- 计算每只股票其余交易日的ema12,ema26,dif和dea字段
    for i in cur_all_stock_index_code loop
      v_code := i.code_;
      -- 用记录是第一个交易日的字段初始化相关变量
      for x in cur_first_stock_index_date loop
        select t.ema12, t.ema26, t.dif, t.dea
          into v_pre_ema12, v_pre_ema26, v_pre_dif, v_pre_dea
          from stock_index t
         where t.code_ = v_code
           and t.date_ = x.date_;
      end loop;
    
      select min(t.date_)
        into v_first_date
        from stock_index t
       where t.code_ = v_code;
      for j in cur_all_stock_index loop
        -- 对于其余交易日，更新ema12,ema26,dif和dea字段
        update stock_index t
           set t.ema12 = v_pre_ema12 * 11 / 13 + j.close_price * 2 / 13,
               t.ema26 = v_pre_ema26 * 25 / 27 + j.close_price * 2 / 27
         where t.code_ = v_code
           and t.date_ = j.date_;
        commit;
        -- 用于计算下一个交易日时使用
        select t.ema12
          into v_pre_ema12
          from stock_index t
         where t.code_ = v_code
           and t.date_ = j.date_;
        select t.ema26
          into v_pre_ema26
          from stock_index t
         where t.code_ = v_code
           and t.date_ = j.date_;
      end loop;
    end loop;
  end WRITE_MACD_EMA;

  procedure WRITE_MACD_DIF as
    v_pre_ema12  number;
    v_pre_ema26  number;
    v_pre_dif    number;
    v_pre_dea    number;
    v_first_date date;
    -- 表示stock_code
    v_code varchar2(10);
    -- define cursor section.返回全部stock_code
    cursor cur_all_stock_index_code is
      select distinct t.code_ from stock_index t order by t.code_ asc;
    -- 获取每只股票第一个交易日的日期
    cursor cur_first_stock_index_date is
      select min(t.date_) as date_
        from stock_index t
       where t.code_ = v_code;
    -- 根据v_code选出某只股票的除第一天以外的全部交易记录，按升序排列
    cursor cur_all_stock_index is
      select distinct *
        from stock_index t
       where t.code_ = v_code
         and t.date_ > v_first_date
       order by t.date_ asc;
  begin
    -- 计算每只股票其余交易日的ema12,ema26,dif和dea字段
    for i in cur_all_stock_index_code loop
      v_code := i.code_;
      -- 用记录是第一个交易日的字段初始化相关变量
      for x in cur_first_stock_index_date loop
        select t.ema12, t.ema26, t.dif, t.dea
          into v_pre_ema12, v_pre_ema26, v_pre_dif, v_pre_dea
          from stock_index t
         where t.code_ = v_code
           and t.date_ = x.date_;
      end loop;
      select min(t.date_)
        into v_first_date
        from stock_index t
       where t.code_ = v_code;
      for j in cur_all_stock_index loop
        update stock_index t
           set t.dif = t.ema12 - t.ema26
         where t.code_ = v_code
           and t.date_ = j.date_;
        commit;
        select t.dif
          into v_pre_dif
          from stock_index t
         where t.code_ = v_code
           and t.date_ = j.date_;
      end loop;
    end loop;
  end WRITE_MACD_DIF;

  procedure WRITE_MACD_DEA as
    v_pre_ema12  number;
    v_pre_ema26  number;
    v_pre_dif    number;
    v_pre_dea    number;
    v_first_date date;
    -- 表示code_
    v_code varchar2(10);
    -- define cursor section.返回全部stock_code
    cursor cur_all_stock_index_code is
      select distinct t.code_ from stock_index t order by t.code_ asc;
    -- 获取每只股票第一个交易日的日期
    cursor cur_first_stock_index_date is
      select min(t.date_) as date_
        from stock_index t
       where t.code_ = v_code;
    -- 根据v_code选出某只股票的除第一天以外的全部交易记录，按升序排列
    cursor cur_all_stock_index is
      select distinct *
        from stock_index t
       where t.code_ = v_code
         and t.date_ > v_first_date
       order by t.date_ asc;
  begin
    -- 计算每只股票其余交易日的ema12,ema26,dif和dea字段
    for i in cur_all_stock_index_code loop
      v_code := i.code_;
      -- 用记录是第一个交易日的字段初始化相关变量
      for x in cur_first_stock_index_date loop
        select t.ema12, t.ema26, t.dif, t.dea
          into v_pre_ema12, v_pre_ema26, v_pre_dif, v_pre_dea
          from stock_index t
         where t.code_ = v_code
           and t.date_ = x.date_;
      end loop;
      select min(t.date_)
        into v_first_date
        from stock_index t
       where t.code_ = v_code;
      for j in cur_all_stock_index loop
        update stock_index t
           set t.dea = v_pre_dea * 8 / 10 + t.dif * 2 / 10
         where t.code_ = v_code
           and t.date_ = j.date_;
        commit;
        select t.dea
          into v_pre_dea
          from stock_index t
         where t.code_ = v_code
           and t.date_ = j.date_;
      end loop;
    end loop;
  end WRITE_MACD_DEA;

  /*-------------------------------- write MACD of all stock index by date -----------------------------------------------*/
  procedure WRITE_MACD_EMA_BY_DATE(p_date in varchar2) as
    v_pre_ema12 number;
    v_pre_ema26 number;
    v_pre_dea   number;
    -- 表示code_
    v_code varchar2(10);
    -- define cursor section.返回全部stock_code
    cursor cur_all_stock_index_code is
      select distinct t.code_ from stock_index t order by t.code_ asc;
    -- 获取某只股票最近两天的记录
    cursor cur_single_stock_index is
      select *
        from (select *
                from stock_index t
               where t.code_ = v_code
                 and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
               order by t.date_ desc)
       where rownum <= 2;
  begin
    for i in cur_all_stock_index_code loop
      v_code := i.code_;
      for j in cur_single_stock_index loop
        if j.date_ != to_date(p_date, 'yyyy-mm-dd') then
          v_pre_ema12 := j.ema12;
          v_pre_ema26 := j.ema26;
          v_pre_dea   := j.dea;
          update stock_index t
             set t.ema12 = v_pre_ema12 * 11 / 13 + t.close_price * 2 / 13,
                 t.ema26 = v_pre_ema26 * 25 / 27 + t.close_price * 2 / 27
           where t.code_ = v_code
             and t.date_ = to_date(p_date, 'yyyy-mm-dd');
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_MACD_EMA_BY_DATE;

  procedure WRITE_MACD_DIF_BY_DATE(p_date in varchar2) as
    v_pre_ema12 number;
    v_pre_ema26 number;
    v_pre_dea   number;
    -- 表示stock_code
    v_code varchar2(10);
    -- define cursor section.返回全部stock_code
    cursor cur_all_stock_index_code is
      select distinct t.code_ from stock_index t order by t.code_ asc;
    -- 获取某只股票最近两天的记录
    cursor cur_single_stock_index is
      select *
        from (select *
                from stock_index t
               where t.code_ = v_code
                 and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
               order by t.date_ desc)
       where rownum <= 2;
  begin
    for i in cur_all_stock_index_code loop
      v_code := i.code_;
      for j in cur_single_stock_index loop
        if j.date_ != to_date(p_date, 'yyyy-mm-dd') then
          v_pre_ema12 := j.ema12;
          v_pre_ema26 := j.ema26;
          v_pre_dea   := j.dea;
          update stock_index t
             set t.dif = t.ema12 - t.ema26
           where t.code_ = v_code
             and t.date_ = to_date(p_date, 'yyyy-mm-dd');
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_MACD_DIF_BY_DATE;

  procedure WRITE_MACD_DEA_BY_DATE(p_date in varchar2) as
    v_pre_ema12 number;
    v_pre_ema26 number;
    v_pre_dea   number;
    -- 表示stock_code
    v_code varchar2(10);
    -- define cursor section.返回全部stock_code
    cursor cur_all_stock_index_code is
      select distinct t.code_ from stock_index t order by t.code_ asc;
    -- 获取某只股票最近两天的记录
    cursor cur_single_stock_index is
      select *
        from (select *
                from stock_index t
               where t.code_ = v_code
                 and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
               order by t.date_ desc)
       where rownum <= 2;
  begin
    for i in cur_all_stock_index_code loop
      v_code := i.code_;
      for j in cur_single_stock_index loop
        if j.date_ != to_date(p_date, 'yyyy-mm-dd') then
          v_pre_ema12 := j.ema12;
          v_pre_ema26 := j.ema26;
          v_pre_dea   := j.dea;
          update stock_index t
             set t.dea = v_pre_dea * 8 / 10 + t.dif * 2 / 10
           where t.code_ = v_code
             and t.date_ = to_date(p_date, 'yyyy-mm-dd');
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_MACD_DEA_BY_DATE;

  /*--------------------------------- 计算日线级别所有指数的KD指标 ----------------------------*/
  procedure WRITE_KD_INIT as
    -- 表示某只指数的code_字段
    v_code varchar2(10);
    -- 用于计算是否是第9个交易周
    num number;
    -- define cursor section.返回全部code_
    cursor cur_all_stock_index_code is
      select distinct t.code_ from stock_index t order by t.code_ asc;
    -- 查询某只指数最初的8个交易周的记录，并按生序排列
    cursor cur_single_stock_index_k is
      select *
        from (select *
                from stock_index t
               where t.code_ = v_code
               order by t.date_ asc)
       where rownum <= 8;
  begin
    -- 初始化每只指数第一个交易日的K和D字段
    for i in cur_all_stock_index_code loop
      v_code := i.code_;
      num    := 0;
      for j in cur_single_stock_index_k loop
        num := num + 1;
        if num = 8 then
          -- 若无前一日K值与D值，则可分别用50来代替
          update stock_index t
             set t.k = 50, t.d = 50
           where t.code_ = v_code
             and t.date_ = j.date_;
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_KD_INIT;

  procedure WRITE_KD_RSV as
    -- 表示某只指数的code_字段
    v_code varchar2(10);
    -- 9日内最高价
    v_nine_day_highest_price number;
    -- 9日内最低价
    v_nine_day_lowest_price number;
    -- 计算指标时使用，用于表示日期的累积
    num number;
    -- define cursor section.返回全部code_
    cursor cur_all_stock_index_code is
      select distinct t.code_ from stock_index t order by t.code_ asc;
    -- 获取某只指数所有的日线级别的交易记录，并按升序排列
    cursor cur_single_stock_index is
      select *
        from stock_index t
       where t.code_ = v_code
       order by t.date_ asc;
  begin
    for i in cur_all_stock_index_code loop
      v_code := i.code_;
      num    := 0;
      for j in cur_single_stock_index loop
        num := num + 1;
        if num >= 9 then
          -- 计算9日内最高价和最低价
          select max(highest_price), min(lowest_price)
            into v_nine_day_highest_price, v_nine_day_lowest_price
            from (select *
                    from stock_index t
                   where t.code_ = v_code
                     and t.date_ <= j.date_
                   order by t.date_ desc)
           where rownum <= 9;
          -- 计算rsv
          update stock_index t
             set t.rsv =
                 (t.close_price - v_nine_day_lowest_price) /
                 (v_nine_day_highest_price - v_nine_day_lowest_price) * 100
           where t.code_ = v_code
             and t.date_ = j.date_;
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_KD_RSV;

  procedure WRITE_KD_K as
    -- 表示某只指数的CODE_字段
    v_code varchar2(10);
    -- 计算指标时使用，用于表示日期的累积
    num number;
    -- 表示KD指标的K
    v_temp_k number;
    -- define cursor section.返回全部code_
    cursor cur_all_stock_index_code is
      select distinct t.code_ from stock_index t order by t.code_ asc;
    -- 获取某只指数所有的日线级别的交易记录，并按升序排列
    cursor cur_single_stock_index is
      select *
        from stock_index t
       where t.code_ = v_code
       order by t.date_ asc;
  begin
    for i in cur_all_stock_index_code loop
      v_code := i.code_;
      num    := 0;
      for j in cur_single_stock_index loop
        num := num + 1;
        if num = 8 then
          v_temp_k := j.k;
        end if;
        if num >= 9 then
          -- 计算K
          update stock_index t
             set t.k = 2 / 3 * v_temp_k + 1 / 3 * t.rsv
           where t.code_ = v_code
             and t.date_ = j.date_;
          commit;
          select t.k
            into v_temp_k
            from stock_index t
           where t.code_ = v_code
             and t.date_ = j.date_;
        end if;
      
      end loop;
    end loop;
  end WRITE_KD_K;

  procedure WRITE_KD_D as
    -- 表示某只指数的code_字段
    v_code varchar2(10);
    -- 计算指标时使用，用于表示日期的累积
    num number;
    -- 表示KD指标的D
    v_temp_d number;
    -- define cursor section.返回全部code_
    cursor cur_all_stock_index_code is
      select distinct t.code_ from stock_index t order by t.code_ asc;
    -- 获取某只指数所有的日线级别的交易记录，并按升序排列
    cursor cur_single_stock_index is
      select *
        from stock_index t
       where t.code_ = v_code
       order by t.date_ asc;
  begin
    for i in cur_all_stock_index_code loop
      v_code := i.code_;
      num    := 0;
      for j in cur_single_stock_index loop
        num := num + 1;
        if num = 8 then
          v_temp_d := j.d;
        end if;
        if num >= 9 then
          -- 计算D
          update stock_index t
             set t.d = 2 / 3 * v_temp_d + 1 / 3 * j.k
           where t.code_ = v_code
             and t.date_ = j.date_;
          commit;
          select t.d
            into v_temp_d
            from stock_index t
           where t.code_ = v_code
             and t.date_ = j.date_;
        end if;
      end loop;
    end loop;
  end WRITE_KD_D;

  ---------------------------------- 计算日线级别，某一日，所有指数的KD指标 ----------------------------
  procedure WRITE_KD_BY_DATE_RSV(p_date varchar2) as
    -- 表示某只指数的code_字段
    v_code varchar2(10);
    -- 9日内最高价
    v_nine_day_highest_price number;
    -- 9日内最低价
    v_nine_day_lowest_price number;
    -- define cursor section.返回全部code_
    cursor cur_all_stock_index_code is
      select distinct t.code_ from stock_index t order by t.code_ asc;
  begin
    for i in cur_all_stock_index_code loop
      v_code := i.code_;
      -- 计算9日内最高价和最低价
      select max(highest_price), min(lowest_price)
        into v_nine_day_highest_price, v_nine_day_lowest_price
        from (select *
                from stock_index t
               where t.code_ = v_code
                 and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
               order by t.date_ desc)
       where rownum <= 9;
      -- 计算某只股票某一日的RSV
      update stock_index t
         set t.rsv =
             (t.close_price - v_nine_day_lowest_price) /
             (v_nine_day_highest_price - v_nine_day_lowest_price) * 100
       where t.code_ = v_code
         and t.date_ = to_date(p_date, 'yyyy-mm-dd');
      commit;
    end loop;
  end WRITE_KD_BY_DATE_RSV;

  procedure WRITE_KD_BY_DATE_K(p_date varchar2) as
    -- 表示某只指数的code_字段
    v_code varchar2(10);
    -- 表示前一日K值
    v_temp_k number;
    -- 用于计数
    v_num number;
    -- define cursor section.返回全部code_
    cursor cur_all_stock_index_code is
      select distinct t.code_ from stock_index t order by t.code_ asc;
    -- 按照日期参数，获取某只指数最近两天的日线级别的交易记录，并按降序排列
    cursor cur_single_stock_index is
      select *
        from (select *
                from stock_index t
               where t.code_ = v_code
                 and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
               order by t.date_ desc)
       where rownum <= 2
       order by date_ asc;
  begin
    for i in cur_all_stock_index_code loop
      v_code := i.code_;
      v_num  := 0;
      for j in cur_single_stock_index loop
        v_num := v_num + 1;
        if v_num = 1 then
          v_temp_k := j.k;
        end if;
        if v_num = 2 then
          update stock_index t
             set t.k = 2 / 3 * v_temp_k + 1 / 3 * t.rsv
           where t.code_ = v_code
             and t.date_ = to_date(p_date, 'yyyy-mm-dd');
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_KD_BY_DATE_K;

  procedure WRITE_KD_BY_DATE_D(p_date varchar2) as
    -- 表示某只指数的code_字段
    v_code varchar2(10);
    -- 表示前一日K值
    v_temp_d number;
    -- 用于计数
    v_num number;
    -- define cursor section.返回全部code_
    cursor cur_all_stock_index_code is
      select distinct t.code_ from stock_index t order by t.code_ asc;
    -- 按照日期参数，获取某只指数最近两天的日线级别的交易记录，并按降序排列
    cursor cur_single_stock_index is
      select *
        from (select *
                from stock_index t
               where t.code_ = v_code
                 and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
               order by t.date_ desc)
       where rownum <= 2
       order by date_ asc;
  begin
    for i in cur_all_stock_index_code loop
      v_code := i.code_;
      v_num  := 0;
      for j in cur_single_stock_index loop
        v_num := v_num + 1;
        if v_num = 1 then
          v_temp_d := j.d;
        end if;
        if v_num = 2 then
          update stock_index t
             set t.d = 2 / 3 * v_temp_d + 1 / 3 * t.k
           where t.code_ = v_code
             and t.date_ = to_date(p_date, 'yyyy-mm-dd');
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_KD_BY_DATE_D;

/*------------------------ calculate stock_index's MACD's gold cross model ----------------------------*/
/*procedure CAL_MDL_STOCK_INDEX_MACD_G_C as
    -- 表示字段MODEL_ID，这个存储过程中是1，在下面初始化
    v_model_id number;
    -- 表示STOCK_CODE
    v_stock_code varchar2(100);
    -- 表示STOCK_DATE
    v_stock_date date;
    -- 表示两天记录中的第一天，用于判断MACD是否发生了金叉或死叉
    v_first_stock_index stock_index%rowtype;
    -- 表示两天记录中的第二天，用于判断MACD是否发生了金叉或死叉
    v_second_stock_index stock_index%rowtype;
    -- 作为临时变量，表示STOCK_CODE,BUY_DATE,SELL_DATE,BUY_PRICE,SELL_PRICE,BUY_DIF,BUY_DEA,SELL_DIF,SELL_DEA
    v_temp_stock_code       varchar2(100);
    v_temp_stock_buy_date   date;
    v_temp_stock_sell_date  date;
    v_temp_stock_buy_price  number;
    v_temp_stock_sell_price number;
    v_temp_stock_buy_dif    number;
    v_temp_stock_buy_dea    number;
    v_temp_stock_sell_dif   number;
    v_temp_stock_sell_dea   number;
    -- 用来判断现在是否是dif>dea的阶段，从而可以判断死叉
    v_start_track_gold_cross_stage number;
    -- 用来表示金叉和死叉是否发现了
    v_gold_cross_found  number;
    v_death_cross_found number;
    -- 每只股票初始的accumulative_profit_loss值为100（表示百分之一百），之后代表上一次交易的累计盈亏百分比
    v_init_last_acc_profit_loss number;
    -- 表示字段PROFIT_LOSS的临时变量
    v_temp_profit_loss number;
    -- 用于获取所有的STOCK_CODE
    cursor cur_all_stock_code is
      select distinct t.code_ from stock_transaction_data_all t; -- where t.code_ like '000%' or t.code_ like '300%' or t.code_ like '600%' or t.code_ like '603%';
    -- 用于根据STOCK_CODE获取某一只股票的所有交易记录
    cursor cur_single_stock is
      select *
        from stock_transaction_data_all t
       where t.code_ = lower(v_stock_code)
       order by t.date_ asc;
    -- 用于获取某一只股票两天的交易记录，用于判断MACD是否发生了金叉或死叉
    cursor cur_single_stock_two_record is
      select *
        from (select *
                from stock_transaction_data_all t
               where t.date_ >= v_stock_date
                 and t.code_ = lower(v_stock_code)
               order by t.date_ asc)
       where rownum <= 2;
  begin
    -- initialize variables
    v_model_id := 1;
  
    for i in cur_all_stock_code loop
      v_stock_code                   := lower(i.code_);
      v_init_last_acc_profit_loss    := 100;
      v_start_track_gold_cross_stage := 0;
      for j in cur_single_stock loop
        v_stock_date := j.date_;
        -- 如果最早的数据是dif>dea，那么接下来只有可能先出现死叉，所以要判断：j.dif<j.dea，也就是说如果第一段数据是dif>dea，则将其忽略
        -- 之后在确认了金叉后，需要确认死叉，所以引入了表示变量v_start_track_gold_cross_stage，当其为1时，表示接下来只可能出现死叉
        if j.dif < j.dea or
           (j.dif > j.dea and v_start_track_gold_cross_stage = 1) then
          -- 分别将两天的记录赋给相应的变量
          for x in cur_single_stock_two_record loop
            if cur_single_stock_two_record%rowcount = 1 then
              v_first_stock := x;
            elsif cur_single_stock_two_record%rowcount = 2 then
              v_second_stock := x;
            end if;
          end loop;
          -- 如果出现了金叉，给临时变量赋值
          if v_first_stock.dif < v_first_stock.dea and
             v_second_stock.dif > v_second_stock.dea and
             v_start_track_gold_cross_stage = 0 then
            v_temp_stock_code      := v_second_stock.code_;
            v_temp_stock_buy_date  := v_second_stock.date_;
            v_temp_stock_buy_price := v_second_stock.close_price;
            v_temp_stock_buy_dif   := v_second_stock.dif;
            v_temp_stock_buy_dea   := v_second_stock.dea;
            v_gold_cross_found     := 1;
            -- 当金叉发生后，只可能发生死叉，因此要将v_start_track_gold_cross_stage设置为1
            v_start_track_gold_cross_stage := 1;
          
            dbms_output.put_line('gold cross   ' || j.date_);
          end if;
          -- 如果出现了死叉，给临时变量赋值
          if v_first_stock.dif > v_first_stock.dea and
             v_second_stock.dif < v_second_stock.dea and
             v_start_track_gold_cross_stage = 1 then
            v_temp_stock_sell_date  := v_second_stock.date_;
            v_temp_stock_sell_price := v_second_stock.close_price;
            v_temp_stock_sell_dif   := v_second_stock.dif;
            v_temp_stock_sell_dea   := v_second_stock.dea;
            v_death_cross_found     := 1;
            -- 当死叉发生后，只可能发生金叉，因此要将v_start_track_gold_cross_stage设置为0
            v_start_track_gold_cross_stage := 0;
          
            dbms_output.put_line('death cross   ' || j.date_);
          end if;
          -- 插入数据。要求这只股票在开始时间和结束时间内没有停牌或除权的情况
          if v_gold_cross_found = 1 and v_death_cross_found = 1 and
             v_model_id <> 0 and v_temp_stock_buy_date is not null and
             v_temp_stock_sell_date is not null and
             v_temp_stock_code is not null then
            v_temp_profit_loss          := round((v_temp_stock_sell_price -
                                                 v_temp_stock_buy_price) /
                                                 v_temp_stock_buy_price,
                                                 4) * 100;
            v_init_last_acc_profit_loss := v_init_last_acc_profit_loss *
                                           (1 + v_temp_profit_loss / 100);
          
            insert into MDL_MACD_GOLD_CROSS
              (model_id,
               buy_date,
               sell_date,
               buy_price,
               sell_price,
               profit_loss,
               stock_code,
               accumulative_profit_loss,
               buy_dif,
               buy_dea,
               sell_dif,
               sell_dea)
            values
              (v_model_id,
               v_temp_stock_buy_date,
               v_temp_stock_sell_date,
               v_temp_stock_buy_price,
               v_temp_stock_sell_price,
               v_temp_profit_loss,
               v_temp_stock_code,
               v_init_last_acc_profit_loss,
               v_temp_stock_buy_dif,
               v_temp_stock_buy_dea,
               v_temp_stock_sell_dif,
               v_temp_stock_sell_dea);
          
            v_gold_cross_found  := 0;
            v_death_cross_found := 0;
          end if;
        end if;
      end loop;
    end loop;
    commit;
  end CAL_MDL_STOCK_INDEX_MACD_G_C;*/

end PKG_INDEX;